package day09.top01.text;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.mysql.jdbc.Driver;
import day09.top01.pojo.Brand;
import org.junit.Test;

import javax.sql.DataSource;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Properties;

public class Text {

    @Test
    public void findAll() throws Exception {

        Class.forName("com.mysql.jdbc.Driver");


        Properties pro = new Properties();

        pro.load(new FileReader("D:\\Java\\group5InClass27\\src\\druid.properties"));

        DataSource ds = DruidDataSourceFactory.createDataSource(pro);

        Connection conn = ds.getConnection();

        String s = "select * from tb_brand";

        PreparedStatement pst = conn.prepareStatement(s);

        ResultSet resultSet = pst.executeQuery();
        ArrayList<Brand> list = new ArrayList<>();
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String brandName = resultSet.getString("brand_name");
            String companyName = resultSet.getString("company_name");
            int ordered = resultSet.getInt("ordered");
            String description = resultSet.getString("description");
            int status = resultSet.getInt("status");

            Brand bd = new Brand(id, brandName, companyName, ordered, description, status);

            list.add(bd);

        }
        System.out.println(list);


        resultSet.close();
        pst.close();
        conn.close();


    }

    @Test
    public void add() throws Exception {

        Properties properties = new Properties();

        properties.load(new FileReader("D:\\Java\\group5InClass27\\src\\druid.properties"));

        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);

        Connection connection = dataSource.getConnection();

        String sql = "insert into tb_brand(brand_name, company_name, ordered, description, status) values(?,?,?,?,?);";


        PreparedStatement pst = connection.prepareStatement(sql);

        String brandName = "热狗";
        String companyName = "阿热";
        Integer ordered = 3;
        String description = "小热";
        Integer status = 1;


        pst.setString(1, brandName);
        pst.setString(2, companyName);
        pst.setInt(3, ordered);
        pst.setString(4, description);
        pst.setInt(5, status);

        int i = pst.executeUpdate();

        System.out.println(i > 0);

        pst.close();
        connection.close();

    }

    @Test
    public void remove() throws Exception {

        Properties pro = new Properties();

        pro.load(new FileReader("D:\\Java\\group5InClass27\\src\\druid.properties"));

        DataSource dataSource = DruidDataSourceFactory.createDataSource(pro);

        Connection connection = dataSource.getConnection();

        String sql = "delete from tb_brand where id = ?";

        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        preparedStatement.setInt(1, 3);

        int i = preparedStatement.executeUpdate();

        System.out.println(i > 0);

        preparedStatement.close();
        connection.close();


    }

    @Test
    public void update() throws Exception {


        Properties properties = new Properties();

        properties.load(new FileReader("D:\\Java\\group5InClass27\\src\\druid.properties"));

        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);

        Connection connection = dataSource.getConnection();

        String sql = "update tb_brand set brand_name=? where id=?";


        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        preparedStatement.setString(1, "小丸");

        preparedStatement.setInt(2, 1);

        int i = preparedStatement.executeUpdate();

        System.out.println(i > 0);

        preparedStatement.close();
        connection.close();

    }


}
